package org.dochandler.dao.hsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.dochandler.config.DHConfig;
import org.dochandler.dao.OfficerDAO;
import org.dochandler.service.domain.Officer;
import org.dochandler.service.domain.Rank;
import org.dochandler.service.exception.DatabaseException;

@SuppressWarnings("serial")
public class HsqlOfficerDAO implements OfficerDAO {

	private Connection conn;
	private String url;
	private String driver;
	
	/**
	 * 
	 * @param args
	 * @throws Exception
	 */
	static public void main(String[] args) throws Exception {
		HsqlOfficerDAO dao = new HsqlOfficerDAO();
		List<Officer> officers = dao.findAll();
		for (Officer o : officers) {
			System.out.printf("Id: %d, Name: %s, Rank %s", o.getId(), o.getName(), o.getRank());
		}
	}
	
	
	public HsqlOfficerDAO() {
		if (conn == null) {
			this.url = DHConfig.getString("global_db_url");
			this.driver = DHConfig.getString("global_db_driver");
	
			try {
				Class.forName(driver).newInstance();
				conn = DriverManager.getConnection("jdbc:hsqldb:file:guardian/dochandler", "sa", "");
			} catch (Exception e) {
				throw new DatabaseException("Could not connect to the database", e);
			}
		}
	}

	@Override
	public Officer findById(Long entityId) {
		Officer officer = null;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM OFFICERS WHERE id=" + entityId);
			
			if (rs.next()) {
				int i=1;
				
				Long id = rs.getLong(i++);
				
				String officerName = rs.getString(i++);
				
				String rankName = rs.getString(i++);
				Rank rank = Rank.fromValue(rankName);
				
				// username not being used
				rs.getString(i++);
				
				String password = rs.getString(i++);
				
				officer = new Officer(id, officerName, rank);
				officer.setPassword(password);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException("Could not execute find all officers", e);
		}
		return officer;
	}

	@Override
	public List<Officer> findAll() {
		List<Officer> list = new ArrayList<Officer>();
		
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM OFFICERS");
			
			while (rs.next()) {
				int i=1;
				
				Long id = rs.getLong(i++);
				
				String officerName = rs.getString(i++);
				
				String rankName = rs.getString(i++);
				Rank rank = Rank.fromValue(rankName);
				
				// username not being used
				rs.getString(i++);
				
				String password = rs.getString(i++);
				
				Officer officer = new Officer(id, officerName, rank);
				officer.setPassword(password);
				
				list.add( officer );
			}
			
		} catch (SQLException e) {
			throw new DatabaseException("Could not execute find all officers", e);
		}
		
		return list;
	}

	@Override
	public boolean create(Officer entity) {
		return false;
	}

	@Override
	public boolean delete(Long entityId) {
		return false;
	}

	@Override
	public boolean update(Officer entity) {
		return false;
	}
	
	
	/*
	 * GETTERS AND SETTERS
	 */

	public Connection getConn() {
		return conn;
	}


	public void setConn(Connection conn) {
		this.conn = conn;
	}


	public String getUrl() {
		return url;
	}


	public void setUrl(String url) {
		this.url = url;
	}


	public String getDriver() {
		return driver;
	}


	public void setDriver(String driver) {
		this.driver = driver;
	}

}
